﻿namespace SRM.DataObjects
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;

    /// <summary>
    /// Summary description for SRM_EducationQualification
    /// </summary>
    [DataObjectAttribute(true)]
    public class SRM_EducationQualification
    {

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetEducationQualification(string applicantKey)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = string.Format("SELECT * FROM SRM_ApplicantEducation_Tbl WHERE ApplicantKey = {0};", applicantKey);
                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        } // public static DataTable GetEducationQualification(string applicantKey)

        [DataObjectMethodAttribute(DataObjectMethodType.Select, true)]
        public static DataTable GetHighestEducationQualification(string applicantKey)
        {
            DataTable dtResult = null;

            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                DataSet ds = new DataSet();
                SqlCommand cmd = conn.CreateCommand();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                cmd.CommandText = string.Format("SELECT * FROM SRM_ApplicantEducation_Tbl WHERE HighestEducation = '1' AND ApplicantKey = {0};", applicantKey);
                da.Fill(ds);

                dtResult = ds.Tables[0];
            }

            return dtResult;
        } // public static DataTable GetHighestEducationQualification(string applicantKey)


        [DataObjectMethodAttribute(DataObjectMethodType.Delete, true)]
        public static void DeleteEducationQualification(string educationKey)
        {
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "DELETE FROM SRM_ApplicantEducation_TBL WHERE EducationKey = @EducationKey;";
                cmd.Parameters.AddWithValue("@EducationKey", educationKey);

                conn.Open();
                cmd.ExecuteNonQuery();
            }
        } // public static void DeleteEmploymentHistory


        /*
<asp:Parameter Name="qualification" Type="String" />
<asp:Parameter Name="discipline" Type="String" />
<asp:Parameter Name="institution" Type="String" />
<asp:Parameter Name="country" Type="String" />
<asp:Parameter Name="durationfrom" Type="String" />
<asp:Parameter Name="durationto" Type="String" />
<asp:Parameter Name="coursemode" Type="String" />
<asp:Parameter Name="timestamp" Type="String" />
<asp:Parameter Name="educationlevelcodename" Type="String" />
<asp:Parameter Name="highesteducation" Type="String" />
<asp:Parameter Name="classgrade" Type="String" />
<asp:Parameter Name="coursename" Type="String" />
<asp:Parameter Name="certobtaindate" Type="String" />
<asp:Parameter Name="graduationyear" Type="String" />
<asp:Parameter Name="otherqualification" Type="String" />
<asp:Parameter Name="educationortraining" Type="String" />
         * */
        [DataObjectMethodAttribute(DataObjectMethodType.Insert, true)]
        public static void InsertEducationQualification(string applicantKey,
            string qualification, string discipline, string institution, string country,
            string durationfrom, string durationto, string coursemode,
            string educationlevelcodename, string highesteducation,
            string classgrade, string coursename, string certobtaindate,
            string graduationyear, string otherqualification, string educationortraining, string certification)
        {
            // INSERT INTO SRM_ApplicantEducation_Tbl (ApplicantKey, Qualification, Discipline, Institution, Country, DurationFrom, DurationTo, CourseMode, Timestamp, EducationLevelCodeName, HighestEducation, ClassGrade, CourseName, CertObtainDate, GraduationYear, OtherQualification, EducationOrTraining) 
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "INSERT INTO SRM_ApplicantEducation_Tbl (ApplicantKey, Qualification, Discipline, Institution, Country, DurationFrom, DurationTo, CourseMode, EducationLevelCodeName, HighestEducation, ClassGrade, CourseName, CertObtainDate, GraduationYear, OtherQualification, EducationOrTraining, Certification) VALUES (@ApplicantKey, @Qualification, @Discipline, @Institution, @Country, @DurationFrom, @DurationTo, @CourseMode, @EducationLevelCodeName, @HighestEducation, @ClassGrade, @CourseName, @CertObtainDate, @GraduationYear, @OtherQualification, @EducationOrTraining, @Certification);";

                //@ApplicantKey,
                //@Qualification,
                //@Discipline,
                //@Institution,
                //@Country,
                //@DurationFrom,

                cmd.Parameters.AddWithValue("@ApplicantKey", Help.GetValueOrDBNull(applicantKey));
                cmd.Parameters.AddWithValue("@Qualification", Help.GetValueOrDBNull(qualification));
                cmd.Parameters.AddWithValue("@Discipline", Help.GetValueOrDBNull(discipline));
                cmd.Parameters.AddWithValue("@Institution", Help.GetValueOrDBNull(institution));
                cmd.Parameters.AddWithValue("@Country", Help.GetValueOrDBNull(country));
                cmd.Parameters.AddWithValue("@DurationFrom", Help.GetValueOrDBNull(durationfrom));

                //@DurationTo,
                //@CourseMode,
                
                //@EducationLevelCodeName,
                //@HighestEducation,
                //@ClassGrade,
                cmd.Parameters.AddWithValue("@DurationTo", Help.GetValueOrDBNull(durationto));
                cmd.Parameters.AddWithValue("@CourseMode", Help.GetValueOrDBNull(coursemode));
                cmd.Parameters.AddWithValue("@EducationLevelCodeName", Help.GetValueOrDBNull(educationlevelcodename));
                cmd.Parameters.AddWithValue("@HighestEducation", Help.GetValueOrDBNull(highesteducation));
                cmd.Parameters.AddWithValue("@ClassGrade", Help.GetValueOrDBNull(classgrade));


                

                //@CourseName,
                //@CertObtainDate,
                //@GraduationYear,
                //@OtherQualification,
                //@EducationOrTraining
                cmd.Parameters.AddWithValue("@CourseName", Help.GetValueOrDBNull(coursename));
                cmd.Parameters.AddWithValue("@CertObtainDate", Help.GetValueOrDBNull(certobtaindate));
                cmd.Parameters.AddWithValue("@GraduationYear", Help.GetValueOrDBNull(graduationyear));
                cmd.Parameters.AddWithValue("@OtherQualification", Help.GetValueOrDBNull(otherqualification));
                cmd.Parameters.AddWithValue("@EducationOrTraining", Help.GetValueOrDBNull(educationortraining));

                cmd.Parameters.AddWithValue("@Certification", Help.GetValueOrDBNull(certification));

                conn.Open();
                cmd.ExecuteNonQuery();
            }

        }

        [DataObjectMethodAttribute(DataObjectMethodType.Update, true)]
        public static void UpdateEducationQualification(string educationKey,
            string qualification, string discipline, string institution, string country,
            string durationfrom, string durationto, string coursemode,
            string educationlevelcodename, string highesteducation,
            string classgrade, string coursename, string certobtaindate,
            string graduationyear, string otherqualification, string educationortraining, string certification)
        {
            // INSERT INTO SRM_ApplicantEducation_Tbl (ApplicantKey, Qualification, Discipline, Institution, Country, DurationFrom, DurationTo, CourseMode, Timestamp, EducationLevelCodeName, HighestEducation, ClassGrade, CourseName, CertObtainDate, GraduationYear, OtherQualification, EducationOrTraining) 
            using (SqlConnection conn = new SqlConnection(Help.GetConnectionString("SRM")))
            {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = @"UPDATE  SRM_ApplicantEducation_Tbl 
SET     Qualification = @Qualification,
        Discipline = @Discipline,
        Institution = @Institution,
        Country = @Country,
        DurationFrom = @DurationFrom,
        DurationTo = @DurationTo,
        CourseMode = @CourseMode,
        EducationLevelCodeName = @EducationLevelCodeName,
        HighestEducation = @HighestEducation,
        ClassGrade = @ClassGrade,
        CourseName = @CourseName,
        CertObtainDate = @CertObtainDate,
        GraduationYear = @GraduationYear,
        OtherQualification = @OtherQualification,
        EducationOrTraining = @EducationOrTraining, 
        Certification = @Certification
WHERE   EducationKey = @EducationKey;";


                cmd.Parameters.AddWithValue("@EducationKey", Help.GetValueOrDBNull(educationKey));
                cmd.Parameters.AddWithValue("@Qualification", Help.GetValueOrDBNull(qualification));
                cmd.Parameters.AddWithValue("@Discipline", Help.GetValueOrDBNull(discipline));
                cmd.Parameters.AddWithValue("@Institution", Help.GetValueOrDBNull(institution));
                cmd.Parameters.AddWithValue("@Country", Help.GetValueOrDBNull(country));
                cmd.Parameters.AddWithValue("@DurationFrom", Help.GetValueOrDBNull(durationfrom));

                //@DurationTo,
                //@CourseMode,

                //@EducationLevelCodeName,
                //@HighestEducation,
                //@ClassGrade,
                cmd.Parameters.AddWithValue("@DurationTo", Help.GetValueOrDBNull(durationto));
                cmd.Parameters.AddWithValue("@CourseMode", Help.GetValueOrDBNull(coursemode));
                cmd.Parameters.AddWithValue("@EducationLevelCodeName", Help.GetValueOrDBNull(educationlevelcodename));
                cmd.Parameters.AddWithValue("@HighestEducation", Help.GetValueOrDBNull(highesteducation));
                cmd.Parameters.AddWithValue("@ClassGrade", Help.GetValueOrDBNull(classgrade));




                //@CourseName,
                //@CertObtainDate,
                //@GraduationYear,
                //@OtherQualification,
                //@EducationOrTraining
                cmd.Parameters.AddWithValue("@CourseName", Help.GetValueOrDBNull(coursename));
                cmd.Parameters.AddWithValue("@CertObtainDate", Help.GetValueOrDBNull(certobtaindate));
                cmd.Parameters.AddWithValue("@GraduationYear", Help.GetValueOrDBNull(graduationyear));
                cmd.Parameters.AddWithValue("@OtherQualification", Help.GetValueOrDBNull(otherqualification));
                cmd.Parameters.AddWithValue("@EducationOrTraining", Help.GetValueOrDBNull(educationortraining));

                cmd.Parameters.AddWithValue("@Certification", Help.GetValueOrDBNull(certification));

                conn.Open();
                cmd.ExecuteNonQuery();
            }

        }

    }
}